<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Admin_commoncall extends CI_Model {

    function __construct() {
        parent::__construct();
        log_message('debug', 'Init Model Initialized');
        $this->load->library('sitemaps');

        if ($_SERVER['HTTP_HOST'] != trim(strtolower('www.infoegypt.com')) && $_SERVER['HTTP_HOST'] != trim(strtolower('infoegypt.com')) && $_SERVER['HTTP_HOST'] != trim(strtolower('localhost'))) {
            $error = 'Sorry! Not Allowed to ' . $_SERVER['HTTP_HOST'];
            $error .= '</BR><b>www.yellowpagesphpscript.com';
            $error .= '</BR>freelance.park@gmail.com</b>';

            print $error;

            die();
        }
    }

    //--Subha (08/04/2012)
    //--News Scroller to Front Pages
    function get_scroller_ticker($type=Null, $no=Null) {
        $this->db->where('type', $type);
        $this->db->limit($no, 'DESC');
        $query = $this->db->get('announcements');
        $result = $query->result();
        return $result;
    }

    //--Subha
    function keyword_name($id=NULL) {
        $this->db->select('keyword')->from('keyword_master');
        $this->db->where('id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        $row = $query->row();
        if ($row) {
            return $row->keyword;
        } else {
            return 0;
        }
    }

    //------joy-------------
    function category_name($id=NULL) {
        $this->db->select('category')->from('keyword_category');
        $this->db->where('id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        $row = $query->row();
        if ($row) {
            return $row->category;
        } else {
            return 0;
        }
    }

//--Subha
    function keyword_listing_count($key_id) {
        if ($key_id) {
            //$sql="SELECT listing_id, count(*) as total FROM listing_keyword T1 WHERE EXISTS(SELECT id FROM listing_master T2 WHERE T2.id = T1.listing_id AND T2.status =51) group by T1.listing_id";
            $sql = "Select count(*)as total from listing_keyword where listing_key_id='$key_id' and status=51";
            $query = $this->db->query($sql);
            $row = $query->row();
            if ($row->total != 0)
                return $row->total;
            else
                return '0';
        }
    }

    function create_keyword_cloud() {

        //--Drop Keyword View
        $sSQL = 'DROP table IF EXISTS keyword_cloud';
        $this->db->query($sSQL);

        //--Create keyword View
        $sSQL = "CREATE TABLE keyword_cloud AS
            (SELECT CONCAT('P') as prefix, id as id, keyword AS keyword FROM keyword_master where status=51) 
            UNION
            (SELECT CONCAT('C') as prefix, id as id, title AS keyword FROM listing_master where status=51)";
        $this->db->query($sSQL);

        //--Create Index for Fast Search
        $sSQL = 'ALTER TABLE keyword_cloud ADD INDEX (keyword)';
        $this->db->query($sSQL);
    }

    function check_public_login() {
        $userdata = $this->session->userdata('sess_member_email');
        if (!$userdata) {
            redirect('member/login');
        }
    }

    function check_admin_login() {
        $userdata = $this->session->userdata('sess_admin_user');
        if (!$userdata) {
            redirect('secure/admin_login');
        }
    }

    function send_mail($email=NULL, $subject=NULL, $body=NULL) {
	
        $this->email->clear();
        //--Email Config
        $email_config['protocol'] = 'mail';
        $email_config['wordwrap'] = FALSE;
        $email_config['mailtype'] = 'html';
        $email_config['charset'] = 'utf-8';
        $email_config['crlf'] = "\r\n";
        $email_config['newline'] = "\r\n";
        $this->email->initialize($email_config);

        $this->email->to($email);
        $this->email->from($this->settings_model->get_setting('default_email'), $this->settings_model->get_setting('webtitle'));
        $this->email->subject($subject);
        $this->email->message($body);
        $this->email->send();
    }

    function dbbackup() {
        // Load the DB utility class
        $this->load->dbutil();

        // Backup your entire database and assign it to a variable
        $backup = & $this->dbutil->backup();

        // Load the file helper and write the file to your server
        //$this->load->helper('file');
        //write_file('/path/to/mybackup.gz', $backup);
        // Load the download helper and send the file to your desktop
        $this->load->helper('download');
        force_download('mybackup.gz', $backup);
    }

    function random_text($lenght=Null) {
        $this->load->helper('string');
        return random_string('alnum', $lenght);
    }

    function count_record($tableName=NULL, $fieldName=NULL, $fieldValue=NULL) {
        $sql = "Select count(*)as total from $tableName where $fieldName=$fieldValue";
        $query = $this->db->query($sql);
        $row = $query->row();
        if ($row->total != 0)
            return $row->total;
        else
            return '0';
    }

    function count_record_by_field($tableName=NULL, $fieldNameOne=NULL, $fieldValueOne=NULL, $fieldNameTwo=NULL, $fieldValueTwo=NULL) {
        $sql = "Select count(*)as total from $tableName where $fieldNameOne=$fieldValueOne and $fieldNameTwo='$fieldValueTwo'";
        $query = $this->db->query($sql);
        $row = $query->row();
        if ($row->total != 0)
            return $row->total;
        else
            return '0';
    }

    function lising_viewed($tableName=NULL, $fieldName=NULL, $fieldValue=NULL, $selectField=NULL) {
        $this->db->select($selectField)->from($tableName);
        $this->db->where($fieldName, $fieldValue);
        $this->db->limit(1);
        $query = $this->db->get();
        $row = $query->row();
        return $row->$selectField;
    }

    //joy
    function get_facilities_by_id($tableName=NULL, $fieldName=Null, $id=NULL) {
        $sql = "SELECT $fieldName as status FROM $tableName where id=$id";
        $query = $this->db->query($sql);
        $result = $query->result();
        $row = $query->row();
        return $row->status;
    }

    //--Subha (31/03/2012)
    function generate_sitemap_listing() {

//--Listing Sitemap
        $this->db->select('*');
        $this->db->where('status', 51);
        $query = $this->db->get('listing_master');
        $result = $query->result();

        $listing = $result;
        foreach ($listing AS $listing) {
            $item = array(
                "loc" => site_url("listing/detail/" . $listing->id . "/" . url_title($listing->title, TRUE)),
                // ISO 8601 format - date("c") requires PHP5
                "lastmod" => date("c", strtotime($listing->modified_on)),
                "changefreq" => "hourly",
                "priority" => ".8"
            );
            $this->sitemaps->add_item($item);
        }
        $sitemap_listing = $this->sitemaps->build("sitemap_listing.xml");
        $reponses = $this->sitemaps->ping(site_url($sitemap_listing));
        // Debug by printing out the requests and status code responses
        //print_r($reponses);
    }

    function generate_sitemap_keyword() {
//--Listing Keyword
        $this->db->select('*');
        $this->db->where('status', 51);
        $query = $this->db->get('keyword_master');
        $result = $query->result();
        $keyword = $result;
        foreach ($keyword AS $keyword) {
            $item = array(
                "loc" => site_url("listing/view/" . $keyword->id . "/" . url_title($keyword->keyword, TRUE)),
                // ISO 8601 format - date("c") requires PHP5
                "lastmod" => date("c", strtotime($keyword->updated_on)),
                "changefreq" => "hourly",
                "priority" => ".8"
            );
            $this->sitemaps->add_item($item);
        }
        $sitemap_keyword = $this->sitemaps->build("sitemap_keyword.xml");

        //--Ping to Google, Yahoo, Bing, Ask
        $reponses = $this->sitemaps->ping(site_url($sitemap_keyword));
    }

    function show_reviews($id=NULL) {
        if ($id) {
            $this->db->select('*')->from('review_master');
            $this->db->where('listing_id', $id);
            $this->db->where('status', '51');
            $this->db->order_by('id', 'asc');
            $query = $this->db->get();
            $result = $query->result();
            return $result;
        }
    }

    function show_all_reviews($num=NULL, $offset=NULL) {

        $this->db->select('*')->from('review_master');
        $this->db->where('status', '51');
        $this->db->order_by('posted_on', 'DESC');
        $this->db->limit($num, $offset);
        $query = $this->db->get();
        $result = $query->result();
        return $result;
    }

    function count_all_reviews() {
        $sql = "SELECT count(id) as 'total' FROM `review_master` WHERE status=51";
        $query = $this->db->query($sql);
        $result = $query->result();
        $row = $query->row();
        return $row->total;
    }

    function subha_facility($listing_id, $fieldName) {
        $this->db->select('type');
        $this->db->where('id', $listing_id);
        $query = $this->db->get('listing_master');
        $mem_id = $query->row()->type;

        $sql = "SELECT $fieldName FROM membership where code=$mem_id";
        $query = $this->db->query($sql);
        $result = $query->result();
        $row = $query->row();
        return $row->$fieldName;
    }

    //--Subha
    function review_ratting_by_listing_id($listing_id) {
        $experience = '0';
        $rate = 0;
		$sql="SELECT sum( experience ) as total FROM `review_master` WHERE listing_id = $listing_id";
		$query = $this->db->query($sql);
        $result = $query->row();
		$total=$result->total;
		if($total){
		$sql = "SELECT COUNT(*) as 'no' FROM review_master WHERE listing_id = $listing_id ";
        $query = $this->db->query($sql);
        $result = $query->row();
		$interval=$result->no;
		$experience=intval($total/$interval);	
		return $experience;
			}
			else{
		return $experience;
		}
    }

    function get_review_rating($listing_id=NULL) {
        $sql = "SELECT experience,COUNT(*) as 'no' FROM review_master WHERE listing_id = $listing_id GROUP BY experience";

        $query = $this->db->query($sql);
        $result = $query->result();
        return $result;
    }

    function adsense_detail() {
        $this->db->select('*')->from('google_adsense');
        $this->db->order_by('id', 'asc');
        $query = $this->db->get();
        $result = $query->result();
        return $result;
    }

    function get_company_name($id=NULL) {
        $sql = "select title from listing_master where id=$id ";
        $query = $this->db->query($sql);
        $result = $query->result();
        $row = $query->row();
        if ($row->title) {
            return $row->title;
        }
        else
            return 0;
    }

    function delete_riview($id=NULL) {
        $this->db->where('id', $id);
        $this->db->delete('review_master');
    }

    function get_anouncement($type=NULL) {
        $sql = "SELECT posted_on,announcement FROM `announcements` where type like '$type' and status=51 order by posted_on limit 0,1 ";
        $query = $this->db->query($sql);
        $result = $query->result();
        return $result;
    }
     function get_pickup() {
        $sql = "select * from pickup ";
        $query = $this->db->query($sql);
        $result = $query->result();
        return $result;
    }
	function getCities(){
	$sql="select * from city where status=51";
	$query=$this->db->query($sql);
	$result=$query->result();
	return $result;
	}

}

?>
